<?php

/**
 * This file is part of the Propel package.
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @license    MIT License
 */

require_once dirname(__FILE__) . '/../BaseSchemaParser.php';

/**
 * Postgresql database schema parser.
 *
 * @author     Hans Lellelid <hans@xmpl.org>
 * @version    $Revision$
 * @package    propel.generator.reverse.pgsql
 */
class PgsqlSchemaParser extends BaseSchemaParser
{

    /**
     * Map PostgreSQL native types to Propel types.
     * @var        array
     */
     /** Map MySQL native types to Propel (JDBC) types. */
    private static $pgsqlTypeMap = array(
                'bool' => PropelTypes::BOOLEAN,
                'boolean' => PropelTypes::BOOLEAN,
                'tinyint' => PropelTypes::TINYINT,
                'smallint' => PropelTypes::SMALLINT,
                'mediumint' => PropelTypes::SMALLINT,
                'int2' => PropelTypes::SMALLINT,
                'int' => PropelTypes::INTEGER,
                'int4' => PropelTypes::INTEGER,
                'serial4' => PropelTypes::INTEGER,
                'integer' => PropelTypes::INTEGER,
                'int8' => PropelTypes::BIGINT,
                'bigint' => PropelTypes::BIGINT,
                'bigserial' => PropelTypes::BIGINT,
                'serial8' => PropelTypes::BIGINT,
                'int24' => PropelTypes::BIGINT,
                'real' => PropelTypes::REAL,
                'float' => PropelTypes::FLOAT,
                'float4' => PropelTypes::FLOAT,
                'decimal' => PropelTypes::DECIMAL,
                'numeric' => PropelTypes::DECIMAL,
                'double' => PropelTypes::DOUBLE,
                'float8' => PropelTypes::DOUBLE,
                'char' => PropelTypes::CHAR,
                'character' => PropelTypes::CHAR,
                'varchar' => PropelTypes::VARCHAR,
                'date' => PropelTypes::DATE,
                'time' => PropelTypes::TIME,
                'timetz' => PropelTypes::TIME,
                //'year' => PropelTypes::YEAR,  PropelTypes::YEAR does not exist... does this need to be mapped to a different propel type?
                'datetime' => PropelTypes::TIMESTAMP,
                'timestamp' => PropelTypes::TIMESTAMP,
                'timestamptz' => PropelTypes::TIMESTAMP,
                'bytea' => PropelTypes::BLOB,
                'text' => PropelTypes::LONGVARCHAR,
    );

    /**
     * Gets a type mapping from native types to Propel types
     *
     * @return array
     */
    protected function getTypeMapping()
    {
        return self::$pgsqlTypeMap;
    }

    /**
     *
     */
    public function parse(Database $database, Task $task = null)
    {
        $stmt = $this->dbh->query("SELECT version() as ver");
        $nativeVersion = $stmt->fetchColumn();

        if (!$nativeVersion) {
            throw new EngineException("Failed to get database version");
        }

        $arrVersion = sscanf($nativeVersion, '%*s %d.%d');
        $version = sprintf("%d.%d", $arrVersion[0], $arrVersion[1]);

        // Clean up
        $stmt = null;

        $stmt = $this->dbh->query("SELECT c.oid,
                                    c.relname, n.nspname
                                    FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)
                                    WHERE c.relkind = 'r'
                                      AND n.nspname NOT IN ('information_schema','pg_catalog')
                                      AND n.nspname NOT LIKE 'pg_temp%'
                                      AND n.nspname NOT LIKE 'pg_toast%'
                                    ORDER BY relname");

        $tableWraps = array();

        // First load the tables (important that this happen before filling out details of tables)
        if ($task) {
            $task->log("Reverse Engineering Tables", Project::MSG_VERBOSE);
        }
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $name = $row['relname'];
            $namespacename = $row['nspname'];
            if ($name == $this->getMigrationTable()) {
                continue;
            }
            if ($task) {
                $task->log("  Adding table '" . $name . "' in schema '" . $namespacename . "'", Project::MSG_VERBOSE);
            }
            $oid = $row['oid'];
            $table = new Table($name);
            if ($namespacename != 'public') {
                $table->setSchema($namespacename);
            }
            $table->setIdMethod($database->getDefaultIdMethod());
            $database->addTable($table);

            // Create a wrapper to hold these tables and their associated OID
            $wrap = new stdClass;
            $wrap->table = $table;
            $wrap->oid = $oid;
            $tableWraps[] = $wrap;
        }

        // Now populate only columns.
        if ($task) {
            $task->log("Reverse Engineering Columns", Project::MSG_VERBOSE);
        }
        foreach ($tableWraps as $wrap) {
            if ($task) {
                $task->log("  Adding columns for table '" . $wrap->table->getName() . "'", Project::MSG_VERBOSE);
            }
            $this->addColumns($wrap->table, $wrap->oid, $version);
        }

        // Now add indexes and constraints.
        if ($task) {
            $task->log("Reverse Engineering Indices And Constraints", Project::MSG_VERBOSE);
        }
        foreach ($tableWraps as $wrap) {
            if ($task) {
                $task->log("  Adding indices and constraints for table '" . $wrap->table->getName() . "'", Project::MSG_VERBOSE);
            }
            $this->addForeignKeys($wrap->table, $wrap->oid, $version);
            $this->addIndexes($wrap->table, $wrap->oid, $version);
            $this->addPrimaryKey($wrap->table, $wrap->oid, $version);
        }

        // TODO - Handle Sequences ...
        return count($tableWraps);
    }

    /**
     * Adds Columns to the specified table.
     *
     * @param Table  $table   The Table model class to add columns to.
     * @param int    $oid     The table OID
     * @param string $version The database version.
     *
     * @throws EngineException
     */
    protected function addColumns(Table $table, $oid, $version)
    {
        // Get the columns, types, etc.
        // Based on code from pgAdmin3 (http://www.pgadmin.org/)
        $stmt = $this->dbh->prepare("SELECT
                                        att.attname,
                                        att.atttypmod,
                                        att.atthasdef,
                                        att.attnotnull,
                                        def.adsrc,
                                        CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
                                        CASE
                                            WHEN ty.typname = 'bpchar'
                                                THEN 'char'
                                            WHEN ty.typname = '_bpchar'
                                                THEN '_char'
                                            ELSE
                                                ty.typname
                                        END AS typname,
                                        ty.typtype
                                    FROM pg_attribute att
                                        JOIN pg_type ty ON ty.oid=att.atttypid
                                        LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
                                    WHERE att.attrelid = ? AND att.attnum > 0
                                        AND att.attisdropped IS FALSE
                                    ORDER BY att.attnum");

        $stmt->bindValue(1, $oid, PDO::PARAM_INT);
        $stmt->execute();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $size = null;
            $precision = null;
            $scale = null;

            // Check to ensure that this column isn't an array data type
            if (((int) $row['isarray']) === 1) {
                throw new EngineException (sprintf("Array datatypes are not currently supported [%s.%s]", $this->name, $row['attname']));
            } // if (((int) $row['isarray']) === 1)

            $name = $row['attname'];

            // If they type is a domain, Process it
            if (strtolower($row['typtype']) == 'd') {
                $arrDomain = $this->processDomain($row['typname']);
                $type = $arrDomain['type'];
                $size = $arrDomain['length'];
                $precision = $size;
                $scale = $arrDomain['scale'];
                $boolHasDefault = (strlen(trim($row['atthasdef'])) > 0) ? $row['atthasdef'] : $arrDomain['hasdefault'];
                $default = (strlen(trim($row['adsrc'])) > 0) ? $row['adsrc'] : $arrDomain['default'];
                $is_nullable = (strlen(trim($row['attnotnull'])) > 0) ? $row['attnotnull'] : $arrDomain['notnull'];
                $is_nullable = (($is_nullable == 't') ? false : true);
            } else {
                $type = $row['typname'];
                $arrLengthPrecision = $this->processLengthScale($row['atttypmod'], $type);
                $size = $arrLengthPrecision['length'];
                $precision = $size;
                $scale = $arrLengthPrecision['scale'];
                $boolHasDefault = $row['atthasdef'];
                $default = $row['adsrc'];
                $is_nullable = (($row['attnotnull'] == 't') ? false : true);
            } // else (strtolower ($row['typtype']) == 'd')

            $autoincrement = null;

            // if column has a default
            if (($boolHasDefault == 't') && (strlen(trim($default)) > 0)) {
                if (!preg_match('/^nextval\(/', $default)) {
                    $strDefault = preg_replace('/::[\W\D]*/', '', $default);
                    $default = str_replace("'", '', $strDefault);
                } else {
                    $autoincrement = true;
                    $default = null;
                }
            } else {
                $default = null;
            }

            $propelType = $this->getMappedPropelType($type);
            if (!$propelType) {
                $propelType = Column::DEFAULT_TYPE;
                $this->warn("Column [" . $table->getName() . "." . $name . "] has a column type (" . $type . ") that Propel does not support.");
            }

            $column = new Column($name);
            $column->setTable($table);
            $column->setDomainForType($propelType);
            // We may want to provide an option to include this:
            // $column->getDomain()->replaceSqlType($type);
            $column->getDomain()->replaceSize($size);
            $column->getDomain()->replaceScale($scale);
            if ($default !== null) {
                if (in_array($default, array('now()'))) {
                    $type = ColumnDefaultValue::TYPE_EXPR;
                } else {
                    $type = ColumnDefaultValue::TYPE_VALUE;
                }
                $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $type));
            }
            $column->setAutoIncrement($autoincrement);
            $column->setNotNull(!$is_nullable);

            $table->addColumn($column);
        }
    } // addColumn()

    private function processLengthScale($intTypmod, $strName)
    {
        // Define the return array
        $arrRetVal = array('length' => null, 'scale' => null);

        // Some datatypes don't have a Typmod
        if ($intTypmod == -1) {
            return $arrRetVal;
        } // if ($intTypmod == -1)

        // Numeric Datatype?
        if ($strName == $this->getMappedNativeType(PropelTypes::NUMERIC)) {
            $intLen = ($intTypmod - 4) >> 16;
            $intPrec = ($intTypmod - 4) & 0xffff;
            $intLen = sprintf("%ld", $intLen);
            if ($intPrec) {
                $intPrec = sprintf("%ld", $intPrec);
            } // if ($intPrec)
            $arrRetVal['length'] = $intLen;
            $arrRetVal['scale'] = $intPrec;
        } // if ($strName == $this->getMappedNativeType(PropelTypes::NUMERIC))
        elseif ($strName == $this->getMappedNativeType(PropelTypes::TIME) || $strName == 'timetz' || $strName == $this->getMappedNativeType(PropelTypes::TIMESTAMP) || $strName == 'timestamptz' || $strName == 'interval' || $strName == 'bit') {
            $arrRetVal['length'] = sprintf("%ld", $intTypmod);
        } // elseif (TIME, TIMESTAMP, INTERVAL, BIT)
        else {
            $arrRetVal['length'] = sprintf("%ld", ($intTypmod - 4));
        } // else

        return $arrRetVal;
    } // private function processLengthScale ($intTypmod, $strName)

    private function processDomain($strDomain)
    {
        if (strlen(trim($strDomain)) < 1) {
            throw new EngineException ("Invalid domain name [" . $strDomain . "]");
        }

        $stmt = $this->dbh->prepare("SELECT
                                        d.typname as domname,
                                        b.typname as basetype,
                                        d.typlen,
                                        d.typtypmod,
                                        d.typnotnull,
                                        d.typdefault
                                    FROM pg_type d
                                        INNER JOIN pg_type b ON b.oid = CASE WHEN d.typndims > 0 then d.typelem ELSE d.typbasetype END
                                    WHERE
                                        d.typtype = 'd'
                                        AND d.typname = ?
                                    ORDER BY d.typname");
        $stmt->bindValue(1, $strDomain);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            throw new EngineException ("Domain [" . $strDomain . "] not found.");
        }

        $arrDomain = array();
        $arrDomain['type'] = $row['basetype'];
        $arrLengthPrecision = $this->processLengthScale($row['typtypmod'], $row['basetype']);
        $arrDomain['length'] = $arrLengthPrecision['length'];
        $arrDomain['scale'] = $arrLengthPrecision['scale'];
        $arrDomain['notnull'] = $row['typnotnull'];
        $arrDomain['default'] = $row['typdefault'];
        $arrDomain['hasdefault'] = (strlen(trim($row['typdefault'])) > 0) ? 't' : 'f';

        $stmt = null; // cleanup

        return $arrDomain;
    } // private function processDomain($strDomain)

    /**
     * Load foreign keys for this table.
     */
    protected function addForeignKeys(Table $table, $oid, $version)
    {
        $database = $table->getDatabase();
        $stmt = $this->dbh->prepare("SELECT
                                          conname,
                                          confupdtype,
                                          confdeltype,
                                          CASE nl.nspname WHEN 'public' THEN cl.relname ELSE nl.nspname||'.'||cl.relname END as fktab,
                                          array_agg(DISTINCT a2.attname) AS fkcols,
                                          CASE nr.nspname WHEN 'public' THEN cr.relname ELSE nr.nspname||'.'||cr.relname END as reftab,
                                          array_agg(DISTINCT a1.attname) AS refcols
                                    FROM pg_constraint ct
                                         JOIN pg_class cl ON cl.oid=conrelid
                                         JOIN pg_class cr ON cr.oid=confrelid
                                         JOIN pg_namespace nl ON nl.oid = cl.relnamespace
                                         JOIN pg_namespace nr ON nr.oid = cr.relnamespace
                                         LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid
                                         LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid
                                    WHERE
                                         contype='f'
                                         AND conrelid = ?
                                          AND a2.attnum = ANY (ct.conkey)
                                          AND a1.attnum = ANY (ct.confkey)
                                    GROUP BY conname, confupdtype, confdeltype, fktab, reftab
                                    ORDER BY conname");
        $stmt->bindValue(1, $oid);
        $stmt->execute();

        $foreignKeys = array(); // local store to avoid duplicates

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $name = $row['conname'];
            $local_table = $row['fktab'];
            $local_columns = explode(',', trim($row['fkcols'], '{}'));
            $foreign_table = $row['reftab'];
            $foreign_columns = explode(',', trim($row['refcols'], '{}'));

            // On Update
            switch ($row['confupdtype']) {
                case 'c':
                    $onupdate = ForeignKey::CASCADE;
                    break;
                case 'd':
                    $onupdate = ForeignKey::SETDEFAULT;
                    break;
                case 'n':
                    $onupdate = ForeignKey::SETNULL;
                    break;
                case 'r':
                    $onupdate = ForeignKey::RESTRICT;
                    break;
                default:
                case 'a':
                    //NOACTION is the postgresql default
                    $onupdate = ForeignKey::NONE;
                    break;
            }
            // On Delete
            switch ($row['confdeltype']) {
                case 'c':
                    $ondelete = ForeignKey::CASCADE;
                    break;
                case 'd':
                    $ondelete = ForeignKey::SETDEFAULT;
                    break;
                case 'n':
                    $ondelete = ForeignKey::SETNULL;
                    break;
                case 'r':
                    $ondelete = ForeignKey::RESTRICT;
                    break;
                default:
                case 'a':
                    //NOACTION is the postgresql default
                    $ondelete = ForeignKey::NONE;
                    break;
            }

            $foreignTable = $database->getTable($foreign_table);
            $localTable = $database->getTable($local_table);

            if (!isset($foreignKeys[$name])) {
                $fk = new ForeignKey($name);
                $fk->setForeignTableCommonName($foreignTable->getCommonName());
                $fk->setForeignSchemaName($foreignTable->getSchema());
                $fk->setOnDelete($ondelete);
                $fk->setOnUpdate($onupdate);
                $table->addForeignKey($fk);
                $foreignKeys[$name] = $fk;
            }

            for ($i = 0; $i < count($local_columns); $i++) {
                $foreignKeys[$name]->addReference(
                    $localTable->getColumn($local_columns[$i]),
                    $foreignTable->getColumn($foreign_columns[$i])
                );
            }
        }
    }

    /**
     * Load indexes for this table
     */
    protected function addIndexes(Table $table, $oid, $version)
    {
        $stmt = $this->dbh->prepare("SELECT
                                        DISTINCT ON(cls.relname)
                                        cls.relname as idxname,
                                        indkey,
                                        indisunique
                                    FROM pg_index idx
                                         JOIN pg_class cls ON cls.oid=indexrelid
                                    WHERE indrelid = ? AND NOT indisprimary
                                    ORDER BY cls.relname");

        $stmt->bindValue(1, $oid);
        $stmt->execute();

        $stmt2 = $this->dbh->prepare("SELECT a.attname
                                        FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
                                        WHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped
                                        ORDER BY a.attnum");

        $indexes = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $name = $row["idxname"];
            $unique = ($row["indisunique"] == 't') ? true : false;
            if (!isset($indexes[$name])) {
                if ($unique) {
                    $indexes[$name] = new Unique($name);
                } else {
                    $indexes[$name] = new Index($name);
                }
                $table->addIndex($indexes[$name]);
            }

            $arrColumns = explode(' ', $row['indkey']);
            foreach ($arrColumns as $intColNum) {
                $stmt2->bindValue(1, $oid);
                $stmt2->bindValue(2, $intColNum);
                $stmt2->execute();

                $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

                $indexes[$name]->addColumn($table->getColumn($row2['attname']));
            } // foreach ($arrColumns as $intColNum)

        }
    }

    /**
     * Loads the primary key for this table.
     */
    protected function addPrimaryKey(Table $table, $oid, $version)
    {

        $stmt = $this->dbh->prepare("SELECT
                                        DISTINCT ON(cls.relname)
                                        cls.relname as idxname,
                                        indkey,
                                        indisunique
                                    FROM pg_index idx
                                        JOIN pg_class cls ON cls.oid=indexrelid
                                    WHERE indrelid = ? AND indisprimary
                                    ORDER BY cls.relname");
        $stmt->bindValue(1, $oid);
        $stmt->execute();

        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $arrColumns = explode(' ', $row['indkey']);
            foreach ($arrColumns as $intColNum) {
                $stmt2 = $this->dbh->prepare("SELECT a.attname
                                                FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
                                                WHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped
                                                ORDER BY a.attnum");
                $stmt2->bindValue(1, $oid);
                $stmt2->bindValue(2, $intColNum);
                $stmt2->execute();

                $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
                $table->getColumn($row2['attname'])->setPrimaryKey(true);
            } // foreach ($arrColumns as $intColNum)
        }
    }

    /**
     * Adds the sequences for this database.
     *
     * @return void
     * @throws SQLException
     */
    protected function addSequences(Database $database)
    {
        /*
        -- WE DON'T HAVE ANY USE FOR THESE YET IN REVERSE ENGINEERING ...
        $this->sequences = array();
        $result = pg_query($this->conn->getResource(), "SELECT c.oid,
                                                        case when n.nspname='public' then c.relname else n.nspname||'.'||c.relname end as relname
                                                        FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)
                                                        WHERE c.relkind = 'S'
                                                          AND n.nspname NOT IN ('information_schema','pg_catalog')
                                                          AND n.nspname NOT LIKE 'pg_temp%'
                                                          AND n.nspname NOT LIKE 'pg_toast%'
                                                        ORDER BY relname");

        if (!$result) {
            throw new SQLException("Could not list sequences", pg_last_error($this->dblink));
        }

        while ($row = pg_fetch_assoc($result)) {
            // FIXME -- decide what info we need for sequences & then create a SequenceInfo object (if needed)
            $obj = new stdClass;
            $obj->name = $row['relname'];
            $obj->oid = $row['oid'];
            $this->sequences[strtoupper($row['relname'])] = $obj;
        }
        */
    }
}
